﻿using API_Service.DBHelper;
using Dapper;
using System;
using System.Data;
using System.Linq;
using System.Reflection;

namespace API_Service.BizLogic.BasicData
{
    public class MatDataMasterService: BaseLogicService
    {
        private string connString = "";
        private string _strDB = "yfdb";
        public MatDataMasterService()
        {
            connString = SqlCreator.ConnectionStrings[this._strDB];            
        }

        public ExecutionResult<dynamic> SyncMatMasterDataFromERP()
        {
            ExecutionResult<dynamic> result = new ExecutionResult<dynamic>();
            try
            {
                string _strMaxCreateTime = "", _strMaxUpdateTime = "";
                //获取MES系统中当前物料最后更新时间。
                result = this.GetMaxMatERPDate();
                if (result.Status)
                {
                    var _tmpDate = result.Datas.AsList()[0];
                    _strMaxCreateTime = String.IsNullOrEmpty(_tmpDate.erp_create_time) ? "19990101" : _tmpDate.erp_create_time;
                    _strMaxUpdateTime = String.IsNullOrEmpty(_tmpDate.erp_update_time) ? "19990101" : _tmpDate.erp_update_time;

                    //获取料头信息
                    KPNPrefixCfgService kpnBaseSrvs = new KPNPrefixCfgService();
                    var lstKPNPrefix = kpnBaseSrvs.GetKPNPreFixString();
                    //获取ERP更新数据
                    result = this.GetERPMatMasterData(_strMaxCreateTime, _strMaxUpdateTime, "P");
                    if (result.Status)
                    {
                        //循环取数，并更新MES库数据
                        foreach(var mat in result.Datas)
                        {
                            bool _bExists = false;
                            if (lstKPNPrefix.Count>0 && !lstKPNPrefix.Contains(mat.key_part_no.Substring(0, 2)))
                            {
                                continue;
                            }
                            //check material is exists
                            var exeRes = this.GetMaterialInfoList(mat.key_part_no);
                            if (exeRes.Status)
                            {
                                var matId = 0;
                                var matFlag = "Y";
                                if (exeRes.Datas.Length > 0)
                                {
                                    matId = exeRes.Datas[0].id;
                                    matFlag = exeRes.Datas[0].is_valid;
                                }
                                if (matFlag == "N")
                                {
                                    continue;
                                }
                                _bExists = matId > 0 ? true : false;
                                if (_bExists)
                                {
                                    //update
                                    mat.update_user = "SYSTEM_JOB";                                    
                                    mat.id = (object)matId;
                                    result = this.UpdateMaterialData(mat);
                                }
                                else
                                {
                                    //insert 
                                    mat.create_user = "SYSTEM_JOB";
                                    
                                    result = this.InsertMaterialData(mat);
                                }
                            }
                            if (!result.Status)
                            {
                                Logger.WriteError(MethodBase.GetCurrentMethod().Name + "-Sync Material error. Msg:" + exeRes.Message);
                                return result;
                            }
                        }
                        result.Status = true;
                        result.Message = "Sync ERP material data success.";
                    }
                }
                Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + "Sync ERP material finished, Msg:" + result.Message);
            }
            catch (Exception ex)
            {
                Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);

                result.Status = false;
                result.Message = ex.Message;
            }

            return result;
        }

        private ExecutionResult<dynamic> GetMaxMatERPDate()
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = "SELECT max(erp_create_time) erp_create_time,max(erp_update_time) erp_update_time FROM dbo.c_material_t WHERE is_valid = 'Y'";
                    conn.Open();
                    result.Datas = conn.Query(sql).ToArray();

                    result.Message = "获取物料数据更新时间成功";
                }
                catch(Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                    result.Status = false;
                    result.Message = ex.Message;
                }
            }
            return result;
        }        
        public ExecutionResult<dynamic> GetERPMatMasterData(string vCreateTime,string vUpdateTime, string vMatProperty)
        {
            var result = new ExecutionResult<dynamic>();
            //链接ERP DB，查询物料信息
            using (IDbConnection conn = SqlCreator.Create(DataBaseType.SqlServer,connString))
            {
                try
                {
                    string sql = @"SELECT TOP 300 MB001 as key_part_no,MB005 as type,MB002 as description, MB003 as spec,MB004 as unit, MB028 as remarks,
                                               CREATE_DATE as erp_create_time,MODI_DATE as erp_update_time 
                                             FROM dbo.INVMB WHERE MB109='Y' AND (CREATE_DATE >@CREATE_DATE or MODI_DATE >@UPDATE_DATE)";
                    if(vMatProperty == "P")
                    {
                        sql = sql + " AND MB025= 'P' ";
                    }
                    else
                    {
                        sql = sql + " AND MB025 <> 'P' ";
                    }

                    result.Datas = conn.Query<dynamic>(sql, new { CREATE_DATE = vCreateTime, UPDATE_DATE = vUpdateTime }).ToArray();

                    result.Status = true;
                    result.Message = "Get ERP Mat master data success.";
                    
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                    result.Status = false;
                    result.Message = ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        public ExecutionResult<dynamic> GetMaterialInfoList(string vKPN = null,string vIsValid = null)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @"SELECT [id],[key_part_no] ,[type] ,[description] ,[spec] ,[unit] ,[remarks]
                          ,[erp_create_time] ,[erp_update_time] ,[is_valid] ,[create_time] ,[create_user] ,[update_time] ,[update_user]
                      FROM [scumes].[dbo].[c_material_t] WHERE 1=1 ";
                    if (!String.IsNullOrEmpty(vKPN))
                    {
                        sql = sql + " AND [key_part_no] = @kpn";
                    }
                    if (!String.IsNullOrEmpty(vIsValid))
                    {
                        sql = sql + " AND [is_valid] = @isValid";
                    }

                    result.Datas = conn.Query(sql, new { kpn = vKPN, isValid = vIsValid }).ToArray();
                    result.Status = true;
                    result.Message = "查询物料基础信息成功.";
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "查询物料基础信息异常。信息:"+ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        private ExecutionResult<dynamic> InsertMaterialData(dynamic vMatInfo)
        {
            var result = new ExecutionResult<dynamic>();
            #region SQL and parameters
            string _insertSql = @"INSERT INTO [dbo].[c_material_t]([key_part_no],[type],[description],[spec],[unit],[remarks]
                                                               ,[erp_create_time],[erp_update_time],[is_valid],[create_time],[create_user])
                                                         VALUES (@key_part_no,@type,@description,@spec,@unit,@remarks,@erp_create_time,@erp_update_time,
                                                                        @is_valid,GETDATE(),@create_user)";
            var dbParams = new DynamicParameters();

            dbParams.Add("@key_part_no", ((string)vMatInfo.key_part_no).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@type", ((string)vMatInfo.type).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@description", ((string)vMatInfo.description).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@spec", ((string)vMatInfo.spec).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@unit", ((string)vMatInfo.unit).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@remarks", ((string)vMatInfo.remarks).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@erp_create_time", ((string)vMatInfo.erp_create_time).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@erp_update_time", ((string)vMatInfo.erp_update_time).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@is_valid","Y", DbType.String, ParameterDirection.Input);            
            dbParams.Add("@create_user", ((string)vMatInfo.create_user).Trim(), DbType.String, ParameterDirection.Input);
            #endregion
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if(result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "添加物料信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "添加物料信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name +" Msg: "+ result.Message + "KPN: " + vMatInfo.key_part_no + "");
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "添加物料信息异常";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        private ExecutionResult<dynamic> UpdateMaterialData(dynamic vMatInfo)
        {
            var result = new ExecutionResult<dynamic>();
            #region SQL and parameters
            string _insertSql = @"UPDATE [dbo].[c_material_t]
                                                           SET [type] = @type,[description] = @description,[spec] = @spec,[unit] = @unit,[remarks] = @remarks,
	                                                           [erp_create_time] = @erp_create_time,[erp_update_time] = @erp_update_time,[is_valid] = @is_valid,
	                                                           [update_time] = GETDATE(),[update_user] = @update_user
                                                         WHERE [id] = @id";
            var dbParams = new DynamicParameters();

            dbParams.Add("@id", (Int32)vMatInfo.id, DbType.Int32, ParameterDirection.Input);
            dbParams.Add("@type", ((String)vMatInfo.type).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@description", ((String)vMatInfo.description).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@spec", ((String)vMatInfo.spec).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@unit", ((String)vMatInfo.unit).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@remarks", ((String)vMatInfo.remarks).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@erp_create_time", ((String)vMatInfo.erp_create_time).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@erp_update_time", ((String)vMatInfo.erp_update_time).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@is_valid", "Y", DbType.String, ParameterDirection.Input);
            //dbParams.Add("@update_time", DateTime.Now, DbType.DateTime, ParameterDirection.Input);
            dbParams.Add("@update_user", (String)vMatInfo.update_user, DbType.String, ParameterDirection.Input);
            #endregion

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "更新物料信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "更新物料信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + " Msg: " + result.Message + "KPN: " + vMatInfo.key_part_no + "");
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "更新物料信息异常";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }

    }
}